
package dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Database Manager met Properties bestand voor Derby, MySQL & PostgreSQL
 * 
 * @author Nico Korthout
 */

public class DBManager {

	private static DBManager dbManager = null;
	private static Connection connection = null;
	
	private static String propertiesFileName = "dbhsql.pro";
	private String driver;
	private String url;
	private String user;
	private String pass;

	/**
	 * SQL statements voor het aanmaken van de tabellen.
	 */
	private static final String SQL_CREATE_TABLE_COLLECTION = "CREATE TABLE "
			+ "Collection (BoxId INTEGER, CDId INTEGER, Artist VARCHAR(30), "
			+ "Album VARCHAR(30), Disk INTEGER, Genre VARCHAR(30), ReleaseYear INTEGER)";

	private PreparedStatement psCreateTableCollection;

	/**
	 * Constructor voor DBManager
	 * Wordt aangeroepen door getInstance()
	 * Hierin wordt de connectie met de database gelegd en
	 * wordt gecheckt of de tabellen al bestaan.
	 */
	private DBManager() {
		System.out.println("-----------Start Connecting to Database-----------");
		this.driver = getProperties("driver");
		this.url = getProperties("url");
		this.user = getProperties("user");
		this.pass = getProperties("pass");

		if(!databaseConnectionExists()) {
			connectToDatabase();
		}
		else {
			System.out.println("Database connection already exists!");
		}
		String[] urlArray = getProperties("url").split(":");
		System.out.println("Connection to " + urlArray[1] + " Database has been established!");

//		createTables();
		System.out.println("Database is ready for use!");
		System.out.println("------------End Connecting to Database------------");
	}

	/**
	 * Checkt of de connectie met de database al bestaat.
	 * @return exists
	 */
	private Boolean databaseConnectionExists() {
		Boolean exists = false;
		try {
			connection = DriverManager.getConnection(url, user, pass);
			exists = true;
		}
		catch(SQLException e) {
			System.out.println("Connection is missing!");
		}
		return (exists);
	}

	/**
	 * Maakt een connectie met de database
	 * aan de hand van de driver.
	 */
	private void connectToDatabase() {
		try {
			System.out.println("Connecting to Database!");
			System.out.println("Using driver:" + driver);
			System.out.println("Using url:" + url);
			System.out.println("Using user:" + user);
			System.out.println("Using pass:" + pass);

			if(driver.equals("org.apache.derby.jdbc.EmbeddedDriver")) {
				String DMgetConnection = url + ";create=true";
				System.out.println("Get connection from: " + DMgetConnection);
				connection = DriverManager.getConnection(DMgetConnection);
			}
			else if(driver.equals("com.mysql.jdbc.Driver")) {
				System.out.println("Get connection from: " + url + ";user=" + user + ";pass=" + pass);
				connection = DriverManager.getConnection(url, user, pass);
			}
			else if(driver.equals("org.postgresql.Driver")) {
				System.out.println("Get connection from: " + url + ";user=" + user + ";pass=" + pass);
				connection = DriverManager.getConnection(url, user, pass);
			}
			else if(driver.equals("org.hsqldb.jdbc.jdbcDriver")) {
				System.out.println("Get connection from: " + url + ";user=" + user + ";pass=" + pass);
				connection = DriverManager.getConnection(url, user, pass);
			}
			else {
				System.out.println("Error reading the Driver from the Properties File!");
				System.out.println("This DatabaseManager is not compatible with this Driver!");
			}
			System.out.println("Connection established!");
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Haalt een gegeven (property) op uit het properties bestand
	 * aangegeven met propertiesFileName
	 * aan de hand van de naam van de op te halen property.
	 * Return null als property niet correct is meegegeven.
	 * @param property ("driver", "url", "user", "pass")
	 * @return value
	 */
	private String getProperties(String property) {
		Properties properties = new Properties();
		FileInputStream inputStream;
		try {
			inputStream = new FileInputStream(propertiesFileName);
			properties.load(inputStream);
			inputStream.close();
		}
		catch(FileNotFoundException e) {
			System.out.println("Database properties file is missing!");
			e.printStackTrace();
		}
		catch(IOException e) {
			e.printStackTrace();
		}
		if(property.equals("driver")) {
			String driver = properties.getProperty("jdbc.driver");
			if(driver != null) {
				System.setProperty("jdbc.driver", driver);
			}
			return driver;
		}
		else if(property.equals("url")) {
			String url = properties.getProperty("jdbc.url");
			if(url != null) {
				System.setProperty("jdbc.url", url);
			}
			return url;
		}
		else if(property.equals("user")) {
			String user = properties.getProperty("jdbc.user");
			return user;
		}
		else if(property.equals("pass")) {
			String pass = properties.getProperty("jdbc.pass");
			return pass;
		}
		else {
			return null;
		}
	}

	/**
	 * Probeert een tabel aan te maken.
	 * In geval van een exceptie bestaat de tabel al en hoeft het niet te worden aangemaakt.
	 */
	private void createTables() {
		try {
			psCreateTableCollection = connection.prepareStatement(SQL_CREATE_TABLE_COLLECTION);
		}
		catch(SQLException e) {
			e.printStackTrace();
			System.out.println("Prepared statement has not been prepared properly!");
		}
		try {
			psCreateTableCollection.executeUpdate();
			System.out.println("Table collection has been created!");
		}
		catch(SQLException e) {
			System.out.println("Table collection already exists!");
		}
	}

	/**
	 * Geef een enkele instantie van DBmanager (singleton)
	 * Maakt zelf de instantie aan als deze nog niet bestaat,
	 * waarna ook de connectie met de database wordt gelegd.
	 * @return singleton instantie van DBmanager
	 */
	public static synchronized DBManager getInstance() {
		if(dbManager == null) {
			dbManager = new DBManager();
		}
		return dbManager;
	}

	/**
	 * Geef een nieuwe bestandsnaam op voor het properties bestand.
	 * @param newPropertiesFileName
	 */
	public static void setPropertiesFileName(String newPropertiesFileName) {
		propertiesFileName = newPropertiesFileName;
	}

	/**
	 * Sluit de connectie met de database.
	 */
	public void close() {
		try {
			connection.close();
			dbManager = null;
			connection = null;
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Geeft de connectie met de database.
	 * @return connection
	 */
	public Connection getConnection() {
		return connection;
	}
}
